package com.ericsson.common.util;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Author: 灵枢
 * Date: 2018/12/05
 * Time: 17:21
 * Description:读取Excel数据
 */
public class ExcelData {
    private XSSFSheet sheet;

    /**
     * 构造函数，初始化excel数据
     * @param filePath  excel路径
     * @param sheetName sheet表名
     */
    ExcelData(String filePath,String sheetName){
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(filePath);
            XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
            //获取sheet
            sheet = sheets.getSheet(sheetName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据行和列的索引获取单元格的数据
     * @param row
     * @param column
     * @return
     */
    public String getExcelDateByIndex(int row,int column){
        XSSFRow row1 = sheet.getRow(row);
        String cell = row1.getCell(column).toString();
        return cell;
    }

    /**
     * 根据某一列值为“******”的这一行，来获取该行第x列的值
     * @param caseName
     * @param currentColumn 当前单元格列的索引
     * @param targetColumn 目标单元格列的索引
     * @return
     */
    public String getCellByCaseName(String caseName,int currentColumn,int targetColumn){
        String operateSteps="";
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            XSSFRow row = sheet.getRow(i);
            String cell = row.getCell(currentColumn).toString();
            if(cell.equals(caseName)){
                operateSteps = row.getCell(targetColumn).toString();
                break;
            }
        }
        return operateSteps;
    }

    //打印excel数据
    public void readExcelData(){
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            //获取列数
            XSSFRow row = sheet.getRow(i);
            int columns = row.getPhysicalNumberOfCells();
            for(int j=0;j<columns;j++){
                String cell = row.getCell(j).toString();
                System.out.println(cell);
            }
        }
    }

    //测试方法
    public static void main(String[] args){
        ExcelData sheet1 = new ExcelData("E://EpcCabVlanDict_LIST.xlsx", "EpcCabVlanDict");
        int rows = sheet1.sheet.getPhysicalNumberOfRows();
        List<EpcCabVlanDict> innerList;
        Map<String,List<EpcCabVlanDict>> innerMap;
        Map<String,Map<String,List<EpcCabVlanDict>>> outerMap = new HashMap<String,Map<String,List<EpcCabVlanDict>>>();
        EpcCabVlanDict EpcCabVlanDict;
        for (int i = 1; i < rows ; i++) {
            XSSFRow row = sheet1.sheet.getRow(i);
            String EpcCabVlanDict_CATEGORY = row.getCell(0).toString();
            XSSFCell cell1 = row.getCell(1);
            String EpcCabVlanDict_VNF_CATEGORY = cell1==null?EpcCabVlanDict_CATEGORY:cell1.toString();
            String EpcCabVlanDict_ID = row.getCell(2).toString();
            XSSFCell cell3 = row.getCell(3);
            String EpcCabVlanDict_DESCRIPTION = cell3==null?EpcCabVlanDict_ID:cell3.toString();
            EpcCabVlanDict = new EpcCabVlanDict();
            EpcCabVlanDict.setEpcCabVlanDict_id(EpcCabVlanDict_ID);
            EpcCabVlanDict.setEpcCabVlanDict_description(EpcCabVlanDict_DESCRIPTION);
            if(outerMap.containsKey(EpcCabVlanDict_CATEGORY)){
                innerMap = outerMap.get(EpcCabVlanDict_CATEGORY);
                if(innerMap.containsKey(EpcCabVlanDict_VNF_CATEGORY)){
                    innerList = innerMap.get(EpcCabVlanDict_VNF_CATEGORY);
                    innerList.add(EpcCabVlanDict);
                }else{
                    innerList = new ArrayList<>();
                    innerList.add(EpcCabVlanDict);
                    innerMap.put(EpcCabVlanDict_VNF_CATEGORY,innerList);
                }
            }else{
                innerList = new ArrayList<>();
                innerMap = new HashMap<String,List<EpcCabVlanDict>>();
                innerList.add(EpcCabVlanDict);
                innerMap.put(EpcCabVlanDict_VNF_CATEGORY,innerList);
                outerMap.put(EpcCabVlanDict_CATEGORY,innerMap);
            }
        }
        //获取第二行第4列
        //String cell2 = sheet1.getExcelDateByIndex(1, 3);
        System.out.println("1111111111");
        //根据第3列值为“customer23”的这一行，来获取该行第2列的值
        //String cell3 = sheet1.getCellByCaseName("customer23", 2,1);
        //System.out.println(cell3);
    }
}

class EpcCabVlanDict_CATEGORY{
    private int id;
    private String EpcCabVlanDict_category;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }
}

class EpcCabVlanDict_VNF_CATEGORY{
    private int id;
    private String EpcCabVlanDict_vnf_category;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }
}

class EpcCabVlanDict{
    private int id;
    private String EpcCabVlanDict_id;
    private String EpcCabVlanDict_description;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getEpcCabVlanDict_id() {
        return EpcCabVlanDict_id;
    }

    public void setEpcCabVlanDict_id(String EpcCabVlanDict_id) {
        this.EpcCabVlanDict_id = EpcCabVlanDict_id;
    }

    public String getEpcCabVlanDict_description() {
        return EpcCabVlanDict_description;
    }

    public void setEpcCabVlanDict_description(String EpcCabVlanDict_description) {
        this.EpcCabVlanDict_description = EpcCabVlanDict_description;
    }
}

